package br.senai.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import br.senai.entity.Aluno;
import br.senai.entity.Materia;
import br.senai.entity.Professor;
import br.senai.util.Conexao;

public class MateriaDAO {
	
	private Connection connection = Conexao.getConnection();
	
	public void adicionarMateria(Materia materia){

		String sql =  "insert into materia (idmateria, nome, descricao, professor) values (?,?,?,?)";
		try {
			PreparedStatement stmt = connection.prepareStatement(sql);
			stmt.setString(1, materia.getCodigoMateria().toString());
			stmt.setString(2, materia.getNomeMateria());
			stmt.setString(3, materia.getDescricao());
			stmt.setString(4, materia.getProfessor().getNome());

			stmt.execute();
			stmt.close();
		} catch (SQLException e) {
			System.out.println(e);
		}

	}
	
	public void editarMateria(Materia materia){
		try {
			PreparedStatement stmt = connection.prepareStatement("update materia set idmateria=?, nome=?, descricao=?, professor=? where idmateria=?");
			stmt.setLong(1, materia.getCodigoMateria());
			stmt.setString(2, materia.getNomeMateria());
			stmt.setString(3, materia.getDescricao());
			stmt.setLong(4, materia.getProfessor().getId_professor());
			stmt.execute();
			stmt.close();

			System.out.println("Contato editado!");
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
		
	}
	
	public void removerMateria(Long id){
		String sql = "delete from materia where idmateria = " + id ;
		try {
			PreparedStatement stmt = connection.prepareStatement(sql);
			stmt.execute();
			stmt.close();
		} catch (Exception e) {
			
		}

	}
	public List<Materia> listarMateria(){
		ResultSet rs;
		List<Materia> listaMateria = new ArrayList<Materia>();
		try{
			PreparedStatement stmt = connection.prepareStatement("select * from materia");
			rs = stmt.executeQuery();
			while(rs.next()){
				Materia materia = new Materia();
				materia.setCodigoMateria(rs.getLong("idmateria"));
				materia.setNomeMateria(rs.getString("nome"));
				materia.setDescricao(rs.getString("descricao"));
				ProfessorDAO pd = new ProfessorDAO();
				Professor professor = pd.buscaProfessor(rs.getLong("professor"));
				materia.setProfessor(professor);
				listaMateria.add(materia);	
			}
			rs.close();
			stmt.close();
		} catch (Exception e) {
			System.out.println(e);
		}
		return listaMateria;
	}
	public Materia buscarMateria(Long id) {
		try {
			PreparedStatement stmt = this.connection.prepareStatement("select * from materia where idmateria =?");
			stmt.setLong(1, id);
			ResultSet rs = stmt.executeQuery();
			Materia materia = null;
			while (rs.next()) {
				
				materia = new Materia();
				materia.setCodigoMateria(rs.getLong("idmateria"));
				materia.setNomeMateria(rs.getString("nome"));
				materia.setDescricao(rs.getString("descricao"));
				
				Professor professor = new Professor();
				professor.setId_professor(Long.valueOf(rs.getString("professor")));
				materia.setProfessor(professor);
			}
			rs.close();
			stmt.close();
			return materia;
		} catch (SQLException e) {
			System.out.println(e.getMessage());
		}
		return null;
	}
	public List<Aluno> buscarAlunosAssociados(Long idMateria) throws SQLException{
		PreparedStatement stmt = this.connection.prepareStatement("select * from  materia_aluno where idmateria = ?");
		ResultSet rs = stmt.executeQuery();
		stmt.setLong(1, idMateria);
		List<Aluno> alunosAssociados = new ArrayList<Aluno>();
		AlunoDAO ad = new AlunoDAO();
		while(rs.next()){
			Aluno aluno = ad.buscarAlunoMatricula(rs.getLong("idaluno"));
			alunosAssociados.add(aluno);
		}
		try {
			rs.close();
			stmt.close();
		} catch (Exception e) {
			e.getMessage();		}
		return alunosAssociados;
	}
	
	public void associarMateriaAluno(Long idMateria,Long idALuno){
		try {
			PreparedStatement stmt = this.connection.prepareStatement("insert into  materia_aluno (idmateria,idaluno) values (?,?)");
			stmt.setLong(1, idMateria);
			stmt.setLong(2, idALuno);
			ResultSet rs = stmt.executeQuery();
			rs.close();
			stmt.close();
		} catch (Exception e) {
			System.out.println("Erro ao associar!");
		}
	}
	public Boolean verAlunoMateriaAssociado(Long idMateria,Long idAluno) throws Throwable{
		Boolean alunoMateria = false;
		try {
			PreparedStatement stmt = this.connection.prepareStatement("select * from  materia_aluno where idmateria = ? and idaluno=?");
			stmt.setLong(1, idMateria);
			stmt.setLong(2, idAluno);
				ResultSet rs = stmt.executeQuery();
				while(rs.next()){
					alunoMateria = true;
				}
				rs.close();
				stmt.close();
		} catch (Exception e) {
				e.getMessage();
		}
		return alunoMateria;
	}
}
